package com.easipass.lgsa.portRelease.util;

import com.easipass.lgsa.portRelease.exception.BusinessTypeException;
import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * maven引入 <dependency> <groupId>com.alibaba</groupId>
 * <artifactId>fastjson</artifactId> <version>1.2.47</version> </dependency>
 * <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId>
 * <version>3.17</version> </dependency> <dependency>
 * <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId>
 * <version>3.17</version> </dependency>
 * <p>
 * excel2Json方法为excel文件转json
 * <p>
 * json2Excel方法为json生成excel
 */

public class ExcelUtil {

    private final Log log = LogFactory.getLog(this.getClass());
    private Gson gson = new Gson();


    public Map<String, Map<String, Object>> excel2Json3(byte[] fileByte, Map<String, String> excelMap, String fileName) throws Exception {

        if (fileName == null || !(isExcel2003(fileName) || isExcel2007(fileName))) {
            throw new BusinessTypeException(fileName + "文件不是excel格式");
        }
        Workbook wb = null;
        InputStream is = new ByteArrayInputStream(fileByte);
        if (this.isExcel2003(fileName)) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Map<String, Map<String, Object>> rs = readExcel(wb, excelMap);
        return rs;
    }

    private String getRelName(String name, Map<String, String> keyNames) {
        if (null != keyNames && keyNames.containsKey(name)) {
            return keyNames.get(name);
        }
        return name;
    }

    public Map<String, Map<String, Object>> readExcel(Workbook wb, Map<String, String> keyNames) throws Exception {
        Map<String, Map<String, Object>> rsList = new LinkedHashMap<>();
        int sheetCount = wb.getNumberOfSheets();

        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) {

            Sheet sheet = wb.getSheetAt(sheetIndex);
            String sheetName = wb.getSheetName(sheetIndex);
            int lastRow = sheet.getLastRowNum();
            if (lastRow == 0) {
                log.error("表单名为" + sheetName + "无数据");
                throw new BusinessTypeException("表单名为" + sheetName + "无数据");
            }
            Row row = null;
            String cellVelue = null;
            Map<String, Object> mainBody = new HashMap<>();
            boolean mainBodyFlag = true;
            String childBodyName = null;

            List<Map<String, Object>> listBody = new ArrayList<>();
            List<String> keys = null;

            row = sheet.getRow(0);
            if (null == row) {
                log.error("表单名为" + sheetName + "第一行无数据");
                throw new BusinessTypeException("表单名为" + sheetName + "第一行无数据");
            }
            cellVelue = cellStr(row.getCell(0));
            if (!"卡口验放凭证#".equals(cellVelue)) {
                log.error("表单名为" + sheetName + "第一行不是‘卡口验放凭证#’");
                throw new BusinessTypeException("表单名为" + sheetName + "第一行不是‘卡口验放凭证#’");
            }
            // 获取excel的结束标志所在行数
            int rowEnd = 0;
            for (int rowIndex = 0; rowIndex <= lastRow; rowIndex++) {
                row = sheet.getRow(rowIndex);
                if (null == row) {
                    continue;
                }
                cellVelue = cellStr(row.getCell(0));
                if (null != cellVelue && (cellVelue.startsWith("END#") || cellVelue.startsWith("end#"))) {
                    rowEnd = rowIndex - 1;
                }
            }
            if (rowEnd == 0) {
                log.error("表单名为" + sheetName + "没有END#结束标志");
                throw new BusinessTypeException("表单名为" + sheetName + "没有END#结束标志");
            }
            if (rowEnd > 500) {
                log.error("sheet行数大于500");
                throw new BusinessTypeException("sheet名为：" + sheetName + "excel数据最多500行");
            }
            // 循环sheet所有行
            for (int rowIndex = 0; rowIndex <= rowEnd; rowIndex++) {
                row = sheet.getRow(rowIndex);
                cellVelue = cellStr(row.getCell(0));
                if (null != cellVelue && cellVelue.startsWith("卡口验放凭证#")) {
                    row = sheet.getRow(++rowIndex);
                    keys = readhead(row);
                    if (!mainBodyFlag && listBody.size() > 0)
                        mainBody.put(getRelName(childBodyName, keyNames), listBody);
                    if (cellVelue.length() > 7) {
                        childBodyName = cellVelue.split("#")[1];
                        mainBodyFlag = false;
                        listBody = new ArrayList<>();
                    }
                } else {
                    if (mainBodyFlag)
                        mainBody.putAll(setBody(keys, row, keyNames));
                    else {
                        Map<String, Object> body = setBody(keys, row, keyNames);
                        listBody.add(body);
                    }
                }
            }
            // 加入最后一个循环体
            if (!mainBodyFlag && listBody.size() > 0)
                mainBody.put(getRelName(childBodyName, keyNames), listBody);
            // rsList.add(mainBody);
            rsList.put(sheetName, mainBody);
        }
        return rsList;
    }

    private List<String> readhead(Row row) {
        List<String> head = new ArrayList<String>();
        int index = 0;
        String colName = null;
        while (true) {
            Cell cell = row.getCell(index++);
            if (cell == null || cell.equals("") || cell.getCellTypeEnum().equals(CellType.BLANK)) {
                break;
            }
            colName = cellStr(cell);
            head.add(colName);
        }
        return head;
    }

    private Map<String, Object> setBody(List<String> keys, Row row, Map<String, String> keyNames) {
        int index = 0;
        Map<String, Object> map = new HashMap<>();
        for (String key : keys) {
            map.put(getRelName(key, keyNames), cellStr(row.getCell(index++)));
        }
        return map;
    }

    private String cellStr(Cell cell) {
        if (cell == null)
            return null;
        switch (cell.getCellTypeEnum()) {
            case STRING:
                return cell == null ? null : cell.getStringCellValue().trim();
            case BLANK:
                return null;
            case NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                    return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    return new DecimalFormat("#").format(cell.getNumericCellValue());
                }
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (IllegalStateException e) {
                    return String.valueOf(cell.getStringCellValue());
                }
        }
        return null;
    }


    private boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    private boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }


}
